﻿/*
Version 1.6.1 Change Log
--------------------
1. Improvements to Crosstab Import Template Configuration Page: characteristic, unit, and activity ID drop-downs added
2. Added new help page
3. Updated Installation/Users Guide
4. Fix import from cross tab when no Activity Type or Activity Media provided to show correct error message.

Version 1.6.2 Change Log
----------------------
5. Cross-tab Activity Data Import Enhancement: new option to include seconds when autogenerating activity ID 
6. Activity Data Import Enhancements: when detecting column data based on header text: (A) now case-insensitive (B) system can now detect column header alias names for certain columns
7. Activity Data Import Enhancements: when importing data that already has matching activity ID's added option to either replace or append to existing data
8. WQX History page expanded: now can also display records pending for WQX transfer and now available for non-global admins
9. Charting Page Improvements: added ability to filter only data shared with EPA; fix monitoring location drop-down; 
10. Public map improvement: added organization to popup window; add organization data filter
11. Activity Details Page: added ability to view/edit more result fields (Laboratory, Lab Sample Prep Method, Prep Date, Dilution Factor)
*/

GO

CREATE TABLE [dbo].[T_WQX_IMPORT_COL_ALIAS](
	[IMPORT_COL_ALIAS_IDX] [int] NOT NULL IDENTITY(1,1),
	[COL_NAME] [varchar](100) NOT NULL,
	[ALIAS_NAME] [varchar](100) NOT NULL,
	[CREATE_DT] [datetime] NULL,
	[CREATE_USERID] [varchar](25) NULL,
 CONSTRAINT [PK_WQX_IMPORT_COL_ALIAS] PRIMARY KEY CLUSTERED ([IMPORT_COL_ALIAS_IDX] ASC)
) ON [PRIMARY]

GO

insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity ID', 'ACTIVITY ID');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity ID', 'SAMPLE ID');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity ID', 'LABSAMPID');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity Media', 'ACTIVITY MEDIA');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity Media', 'MEDIA');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity Media', 'MATRIX');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity Start Date', 'ACTIVITY START DATE');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity Start Date', 'SAMPLE DATE');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity Start Date', 'SAMPDATE');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity Start Time', 'ACTIVITY START TIME');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity Start Time', 'SAMPLE TIME');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Activity Start Time', 'SAMPTIME');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Lab Name', 'LAB NAME');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Lab Name', 'LABNAME');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Lab Name', 'LAB');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Characteristic', 'CHARACTERISTIC');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Characteristic', 'ANALYTE');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Sample Prep ID', 'SAMPLE PREP ID');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Sample Prep ID', 'PREPNAME');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Sample Prep ID', 'PREPID');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Unit', 'UNIT');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Unit', 'UNITS_RESULT');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Result Detection Condition', 'RESULT DETECTION CONDITION');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Result Detection Condition', 'DETECTIONCONDITION');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Result Detection Condition', 'DETECTION CONDITION');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Dilution Factor', 'DILUTION FACTOR');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Dilution Factor', 'DILUTION');

insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Detection Threshold Limit Unit', 'DETECTION THRESHOLD LIMIT UNIT');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Detection Threshold Limit Unit', 'UNITS_DL');
insert into T_WQX_IMPORT_COL_ALIAS ([COL_NAME], [ALIAS_NAME]) values ('Detection Threshold Limit Unit', 'DETECTION LIMIT UNIT');


GO

CREATE VIEW V_WQX_PENDING_RECORDS
as
select ACTIVITY_IDX as REC_IDX, 'ACT' as TABLE_CD, ACTIVITY_ID as REC_ID, ORG_ID, COALESCE(UPDATE_USERID, CREATE_USERID) as UPDATE_USERID, COALESCE(UPDATE_DT, CREATE_DT) as UPDATE_DT from T_WQX_ACTIVITY where WQX_IND=1 and WQX_SUBMIT_STATUS='U'
UNION
select MONLOC_IDX as REC_IDX, 'MLOC' as TABLE_CD, MONLOC_ID as REC_ID, ORG_ID, COALESCE(UPDATE_USERID, CREATE_USERID) as UPDATE_USERID, COALESCE(UPDATE_DT, CREATE_DT) as UPDATE_DT from T_WQX_MONLOC where WQX_IND=1 and WQX_SUBMIT_STATUS='U'
UNION
select PROJECT_IDX as REC_IDX, 'PROJ' as TABLE_CD, PROJECT_ID as REC_ID, ORG_ID, COALESCE(UPDATE_USERID, CREATE_USERID) as UPDATE_USERID, COALESCE(UPDATE_DT, CREATE_DT) as UPDATE_DT from T_WQX_PROJECT where WQX_IND=1 and WQX_SUBMIT_STATUS='U';


GO

drop view V_WQX_ACTIVITY_LATEST;

GO

create view V_WQX_ACTIVITY_LATEST as
select A.ACTIVITY_IDX, A.ORG_ID, A.PROJECT_IDX, A.MONLOC_IDX, MM.MONLOC_NAME, A.ACTIVITY_ID, A.ACT_TYPE, A.ACT_START_DT, A.WQX_IND, A.CREATE_DT, A.CREATE_USERID, A.ACT_COMMENT
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Alkalinity, total') AS 'Alkalinity, total'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Ammonia') AS 'Ammonia'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Dissolved oxygen (DO)') AS 'Dissolved oxygen (DO)'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Escherichia coli') AS 'Escherichia coli'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Nitrate') AS 'Nitrate'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Nitrite') AS 'Nitrite'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='pH') AS 'pH'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Phosphorus') AS 'Phosphorus'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Salinity') AS 'Salinity'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Specific Conductance') AS 'Specific Conductance'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Temperature, air') AS 'Temperature, air'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Temperature, water') AS 'Temperature, water'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Total Dissolved Solids') AS 'Total Dissolved Solids'
,(select top 1 R.RESULT_MSR from T_WQX_RESULT R where R.ACTIVITY_IDX=A.ACTIVITY_IDX and R.CHAR_NAME='Turbidity') AS 'Turbidity'
from T_WQX_MONLOC MM
left join T_WQX_ACTIVITY A on MM.MONLOC_IDX = A.MONLOC_IDX and A.ACT_START_DT = (select MAX(AAA.ACT_START_DT) from T_WQX_ACTIVITY AAA where AAA.MONLOC_IDX = MM.MONLOC_IDX)
where MM.ACT_IND=1;


GO



ALTER PROCEDURE [dbo].[WQXAnalysis]

@TypeText varchar(10),
@OrgID varchar(20),
@MonLocID varchar(20),
@CharName varchar(200),
@StartDt datetime,
@EndDt datetime,
@DataIncludeInd varchar(1)
AS
BEGIN
	/*
	DESCRIPTION: RETURNS DATA FOR CHARTING
	CHANGE LOG: 6/29/2012 DOUG TIMMS, OPEN-ENVIRONMENT.ORG
	4/23/2015 properly add Org filter
	*/
	SET NOCOUNT ON;

	if @TypeText='SERIES' 
		BEGIN
			select case when min(M.MONLOC_NAME)<>MAX(M.MONLOC_NAME) then 'Multiple' else min(M.MONLOC_NAME) end as MONLOC_NAME, 
				MIN(R.CHAR_NAME) as CHAR_NAME,
				dateadd(dd, datediff(dd, 0, a.ACT_START_DT)+0, 0) as START_DT, 
				avg(

case when ISNUMERIC(RESULT_MSR)=1 then cast(RESULT_MSR as DECIMAL(10,4))
when ISNUMERIC( DETECTION_LIMIT)=1 then cast(DETECTION_LIMIT as DECIMAL(10,4))
when ISNUMERIC(LAB_REPORTING_LEVEL)=1 then cast(LAB_REPORTING_LEVEL as DECIMAL(10,4))
when ISNUMERIC(PQL)=1 then cast (PQL as DECIMAL(10,4))
when ISNUMERIC(LOWER_QUANT_LIMIT)=1 then cast (LOWER_QUANT_LIMIT as DECIMAL(10,4))
when ISNUMERIC(UPPER_QUANT_LIMIT)=1 then cast (UPPER_QUANT_LIMIT as DECIMAL(10,4))
else CAST(0 as DECIMAL(10,4)) end 


				) as RESULT_MSR, 
				min(R.RESULT_MSR_UNIT) as RESULT_MSR_UNIT,
				min(R.DETECTION_LIMIT) as DETECTION_LIMIT
			from T_WQX_RESULT R, T_WQX_ACTIVITY A, T_WQX_MONLOC M
			where A.ACTIVITY_IDX = R.ACTIVITY_IDX
			and M.MONLOC_IDX = A.MONLOC_IDX
			and R.CHAR_NAME = @CharName
			and (A.ORG_ID = @OrgID or @OrgID is null)
			and (A.ACT_START_DT >= @StartDt or @StartDt is null)
			and (A.ACT_START_DT <= @EndDt or @EndDt is null)
			and (A.MONLOC_IDX = @MonLocID or @MonLocID = 0)
			and (@DataIncludeInd = 'A' or A.WQX_IND = 1)
			group by dateadd(dd, datediff(dd, 0, a.ACT_START_DT)+0, 0) 
			order by dateadd(dd, datediff(dd, 0, a.ACT_START_DT)+0, 0)
		END
	
END

GO



ALTER PROCEDURE [dbo].[ImportActivityFromTemp]
  @UserID varchar(25),
  @WQXInd varchar(1),
  @ActivityReplaceInd varchar(1)
AS
BEGIN
	/*
	DESCRIPTION: COPIES DATA FROM TEMP ACTIVITY AND RESULT TABLES INTO PERMANENT TABLES
	CHANGE LOG: 3/14/2015 DOUG TIMMS, OPEN-ENVIRONMENT.ORG
	5/2/2015 DOUG TIMMS: added ability to update matching activity

	@ActivityReplaceInd: "R": delete existing activity and replace with new one (*recommended*)
						 "U": update existing activity (appending results if found)  (*not recommended)						 
	*/
	SET NOCOUNT ON;

	DECLARE @WQXIndBool bit;
	if @WQXInd='Y' 
		set @WQXIndBool=1;
	else
		set @WQXIndBool=0;


    --delete matching activities 
	if @ActivityReplaceInd='R'
	BEGIN
		delete from T_WQX_ACTIVITY where ACTIVITY_ID in (select ACTIVITY_ID from T_WQX_IMPORT_TEMP_SAMPLE where UPPER(USER_ID)= UPPER(@UserID));
	END


	--insert and update activity case
    merge into T_WQX_ACTIVITY A
    USING T_WQX_IMPORT_TEMP_SAMPLE T
    ON A.ACTIVITY_ID = T.ACTIVITY_ID and T.IMPORT_STATUS_CD = 'P' and UPPER(USER_ID) = UPPER('KAP')
    when MATCHED then 
	    UPDATE SET A.PROJECT_IDX = T.PROJECT_IDX, A.MONLOC_IDX = T.MONLOC_IDX, A.ACT_TYPE = T.ACT_TYPE, A.ACT_MEDIA = T.ACT_MEDIA, A.ACT_SUBMEDIA = T.ACT_SUBMEDIA, 
		A.ACT_START_DT = T.ACT_START_DT, A.ACT_END_DT = T.ACT_END_DT, A.ACT_TIME_ZONE = T.ACT_TIME_ZONE, A.RELATIVE_DEPTH_NAME = T.RELATIVE_DEPTH_NAME, 
		A.ACT_DEPTHHEIGHT_MSR = T.ACT_DEPTHHEIGHT_MSR, A.ACT_DEPTHHEIGHT_MSR_UNIT = T.ACT_DEPTHHEIGHT_MSR_UNIT, A.TOP_DEPTHHEIGHT_MSR = T.TOP_DEPTHHEIGHT_MSR, 
		A.TOP_DEPTHHEIGHT_MSR_UNIT = T.TOP_DEPTHHEIGHT_MSR_UNIT, A.BOT_DEPTHHEIGHT_MSR = T.BOT_DEPTHHEIGHT_MSR, A.BOT_DEPTHHEIGHT_MSR_UNIT = T.BOT_DEPTHHEIGHT_MSR_UNIT, 
		A.DEPTH_REF_POINT = T.DEPTH_REF_POINT, A.ACT_COMMENT = T.ACT_COMMENT, A.BIO_ASSEMBLAGE_SAMPLED = T.BIO_ASSEMBLAGE_SAMPLED, A.BIO_DURATION_MSR = T.BIO_DURATION_MSR, 
		A.BIO_DURATION_MSR_UNIT = T.BIO_DURATION_MSR_UNIT, A.BIO_SAMP_COMPONENT = T.BIO_SAMP_COMPONENT, A.BIO_SAMP_COMPONENT_SEQ = T.BIO_SAMP_COMPONENT_SEQ, 
		A.BIO_REACH_LEN_MSR = T.BIO_REACH_LEN_MSR, A.BIO_REACH_LEN_MSR_UNIT = T.BIO_REACH_LEN_MSR_UNIT, A.BIO_REACH_WID_MSR = T.BIO_REACH_WID_MSR, A.BIO_REACH_WID_MSR_UNIT = T.BIO_REACH_WID_MSR_UNIT, 
		A.BIO_PASS_COUNT = T.BIO_PASS_COUNT, A.BIO_NET_TYPE = T.BIO_NET_TYPE, A.BIO_NET_AREA_MSR = T.BIO_NET_AREA_MSR, A.BIO_NET_AREA_MSR_UNIT = T.BIO_NET_AREA_MSR_UNIT, 
		A.BIO_NET_MESHSIZE_MSR = T.BIO_NET_MESHSIZE_MSR, A.BIO_MESHSIZE_MSR_UNIT = T.BIO_MESHSIZE_MSR_UNIT, A.BIO_BOAT_SPEED_MSR = T.BIO_BOAT_SPEED_MSR, 
		A.BIO_BOAT_SPEED_MSR_UNIT = T.BIO_BOAT_SPEED_MSR_UNIT, A.BIO_CURR_SPEED_MSR = T.BIO_CURR_SPEED_MSR, A.BIO_CURR_SPEED_MSR_UNIT = T.BIO_CURR_SPEED_MSR_UNIT, 
		A.BIO_TOXICITY_TEST_TYPE = T.BIO_TOXICITY_TEST_TYPE, A.SAMP_COLL_METHOD_IDX = T.SAMP_COLL_METHOD_IDX, A.SAMP_COLL_EQUIP = T.SAMP_COLL_EQUIP, 
		A.SAMP_COLL_EQUIP_COMMENT = T.SAMP_COLL_EQUIP_COMMENT, A.SAMP_PREP_IDX = T.SAMP_PREP_IDX, A.SAMP_PREP_CONT_TYPE = T.SAMP_PREP_CONT_TYPE, A.SAMP_PREP_CONT_COLOR = T.SAMP_PREP_CONT_COLOR,
		A.SAMP_PREP_CHEM_PRESERV = T.SAMP_PREP_CHEM_PRESERV, A.SAMP_PREP_THERM_PRESERV = T.SAMP_PREP_THERM_PRESERV, A.SAMP_PREP_STORAGE_DESC = T.SAMP_PREP_STORAGE_DESC,
		A.UPDATE_DT = GetDate(), A.UPDATE_USERID = @UserID, A.ACT_IND = 1, A.WQX_IND = @WQXIndBool, A.WQX_SUBMIT_STATUS = 'U', A.TEMP_SAMPLE_IDX = T.TEMP_SAMPLE_IDX
    when NOT MATCHED then 
		INSERT (ORG_ID, PROJECT_IDX, MONLOC_IDX, ACTIVITY_ID, ACT_TYPE, ACT_MEDIA, ACT_SUBMEDIA, ACT_START_DT, ACT_END_DT, ACT_TIME_ZONE, RELATIVE_DEPTH_NAME, ACT_DEPTHHEIGHT_MSR, ACT_DEPTHHEIGHT_MSR_UNIT, 
		TOP_DEPTHHEIGHT_MSR, TOP_DEPTHHEIGHT_MSR_UNIT, BOT_DEPTHHEIGHT_MSR, BOT_DEPTHHEIGHT_MSR_UNIT, DEPTH_REF_POINT, ACT_COMMENT, BIO_ASSEMBLAGE_SAMPLED, BIO_DURATION_MSR, 
		BIO_DURATION_MSR_UNIT, BIO_SAMP_COMPONENT, BIO_SAMP_COMPONENT_SEQ, BIO_REACH_LEN_MSR, BIO_REACH_LEN_MSR_UNIT, BIO_REACH_WID_MSR, BIO_REACH_WID_MSR_UNIT, BIO_PASS_COUNT,
		BIO_NET_TYPE, BIO_NET_AREA_MSR, BIO_NET_AREA_MSR_UNIT, BIO_NET_MESHSIZE_MSR, BIO_MESHSIZE_MSR_UNIT, BIO_BOAT_SPEED_MSR, BIO_BOAT_SPEED_MSR_UNIT, BIO_CURR_SPEED_MSR, 
		BIO_CURR_SPEED_MSR_UNIT, BIO_TOXICITY_TEST_TYPE, SAMP_COLL_METHOD_IDX, SAMP_COLL_EQUIP, SAMP_COLL_EQUIP_COMMENT, SAMP_PREP_IDX, SAMP_PREP_CONT_TYPE, SAMP_PREP_CONT_COLOR,
		SAMP_PREP_CHEM_PRESERV, SAMP_PREP_THERM_PRESERV, SAMP_PREP_STORAGE_DESC, CREATE_DT, CREATE_USERID, ACT_IND, WQX_IND, WQX_SUBMIT_STATUS, TEMP_SAMPLE_IDX)
		VALUES (T.ORG_ID, T.PROJECT_IDX, T.MONLOC_IDX, T.ACTIVITY_ID, T.ACT_TYPE, T.ACT_MEDIA, T.ACT_SUBMEDIA, T.ACT_START_DT, T.ACT_END_DT, T.ACT_TIME_ZONE, T.RELATIVE_DEPTH_NAME, 
		T.ACT_DEPTHHEIGHT_MSR, T.ACT_DEPTHHEIGHT_MSR_UNIT, T.TOP_DEPTHHEIGHT_MSR, T.TOP_DEPTHHEIGHT_MSR_UNIT, T.BOT_DEPTHHEIGHT_MSR, T.BOT_DEPTHHEIGHT_MSR_UNIT, T.DEPTH_REF_POINT, 
		T.ACT_COMMENT, T.BIO_ASSEMBLAGE_SAMPLED, T.BIO_DURATION_MSR, T.BIO_DURATION_MSR_UNIT, T.BIO_SAMP_COMPONENT, T.BIO_SAMP_COMPONENT_SEQ, T.BIO_REACH_LEN_MSR, T.BIO_REACH_LEN_MSR_UNIT, 
		T.BIO_REACH_WID_MSR, T.BIO_REACH_WID_MSR_UNIT, T.BIO_PASS_COUNT, T.BIO_NET_TYPE, T.BIO_NET_AREA_MSR, T.BIO_NET_AREA_MSR_UNIT, T.BIO_NET_MESHSIZE_MSR, T.BIO_MESHSIZE_MSR_UNIT, 
		T.BIO_BOAT_SPEED_MSR, T.BIO_BOAT_SPEED_MSR_UNIT, T.BIO_CURR_SPEED_MSR,  T.BIO_CURR_SPEED_MSR_UNIT, T.BIO_TOXICITY_TEST_TYPE, T.SAMP_COLL_METHOD_IDX, T.SAMP_COLL_EQUIP, 
		T.SAMP_COLL_EQUIP_COMMENT, T.SAMP_PREP_IDX, T.SAMP_PREP_CONT_TYPE, T.SAMP_PREP_CONT_COLOR, T.SAMP_PREP_CHEM_PRESERV, T.SAMP_PREP_THERM_PRESERV, T.SAMP_PREP_STORAGE_DESC, 
		GetDate(), @UserID, 1, @WQXIndBool, 'U', T.TEMP_SAMPLE_IDX);



	--insert result
	insert into T_WQX_RESULT (ACTIVITY_IDX,	DATA_LOGGER_LINE, RESULT_DETECT_CONDITION, CHAR_NAME, METHOD_SPECIATION_NAME, RESULT_SAMP_FRACTION, RESULT_MSR, RESULT_MSR_UNIT, RESULT_MSR_QUAL, 
	RESULT_STATUS, STATISTIC_BASE_CODE, RESULT_VALUE_TYPE, WEIGHT_BASIS, TIME_BASIS, TEMP_BASIS, PARTICLESIZE_BASIS, PRECISION_VALUE, BIAS_VALUE, 
	CONFIDENCE_INTERVAL_VALUE, UPPER_CONFIDENCE_LIMIT, LOWER_CONFIDENCE_LIMIT, RESULT_COMMENT, DEPTH_HEIGHT_MSR, DEPTH_HEIGHT_MSR_UNIT, DEPTHALTITUDEREFPOINT, 
	BIO_INTENT_NAME, BIO_INDIVIDUAL_ID, BIO_SUBJECT_TAXONOMY, BIO_UNIDENTIFIED_SPECIES_ID, BIO_SAMPLE_TISSUE_ANATOMY, GRP_SUMM_COUNT_WEIGHT_MSR, GRP_SUMM_COUNT_WEIGHT_MSR_UNIT, 
	TAX_DTL_CELL_FORM, TAX_DTL_CELL_SHAPE, TAX_DTL_HABIT, TAX_DTL_VOLTINISM, TAX_DTL_POLL_TOLERANCE, TAX_DTL_POLL_TOLERANCE_SCALE, TAX_DTL_TROPHIC_LEVEL, 
	TAX_DTL_FUNC_FEEDING_GROUP1, TAX_DTL_FUNC_FEEDING_GROUP2, TAX_DTL_FUNC_FEEDING_GROUP3, [FREQ_CLASS_CODE], [FREQ_CLASS_UNIT], [FREQ_CLASS_UPPER], [FREQ_CLASS_LOWER], 
	ANALYTIC_METHOD_IDX, LAB_IDX, LAB_ANALYSIS_START_DT, 
	LAB_ANALYSIS_END_DT, LAB_ANALYSIS_TIMEZONE, RESULT_LAB_COMMENT_CODE, DETECTION_LIMIT, LAB_REPORTING_LEVEL, PQL, LOWER_QUANT_LIMIT, UPPER_QUANT_LIMIT,
	DETECTION_LIMIT_UNIT, LAB_SAMP_PREP_IDX, LAB_SAMP_PREP_START_DT, LAB_SAMP_PREP_END_DT, DILUTION_FACTOR
	)
	select A.ACTIVITY_IDX, R.DATA_LOGGER_LINE, R.RESULT_DETECT_CONDITION, R.CHAR_NAME, R.METHOD_SPECIATION_NAME, R.RESULT_SAMP_FRACTION, R.RESULT_MSR, R.RESULT_MSR_UNIT, R.RESULT_MSR_QUAL, 
	R.RESULT_STATUS, R.STATISTIC_BASE_CODE, R.RESULT_VALUE_TYPE, R.WEIGHT_BASIS, R.TIME_BASIS, R.TEMP_BASIS, R.PARTICLESIZE_BASIS, R.PRECISION_VALUE, R.BIAS_VALUE, 
	R.CONFIDENCE_INTERVAL_VALUE, R.UPPER_CONFIDENCE_LIMIT, R.LOWER_CONFIDENCE_LIMIT, R.RESULT_COMMENT, R.DEPTH_HEIGHT_MSR, R.DEPTH_HEIGHT_MSR_UNIT, R.DEPTHALTITUDEREFPOINT, 
	R.BIO_INTENT_NAME, R.BIO_INDIVIDUAL_ID, R.BIO_SUBJECT_TAXONOMY, R.BIO_UNIDENTIFIED_SPECIES_ID, R.BIO_SAMPLE_TISSUE_ANATOMY, R.GRP_SUMM_COUNT_WEIGHT_MSR, R.GRP_SUMM_COUNT_WEIGHT_MSR_UNIT, 
	R.TAX_DTL_CELL_FORM, r.TAX_DTL_CELL_SHAPE, r.TAX_DTL_HABIT, r.TAX_DTL_VOLTINISM, r.TAX_DTL_POLL_TOLERANCE, r.TAX_DTL_POLL_TOLERANCE_SCALE, r.TAX_DTL_TROPHIC_LEVEL, 
	r.TAX_DTL_FUNC_FEEDING_GROUP1, r.TAX_DTL_FUNC_FEEDING_GROUP2, r.TAX_DTL_FUNC_FEEDING_GROUP3, r.FREQ_CLASS_CODE, r.FREQ_CLASS_UNIT, r.FREQ_CLASS_UPPER, r.FREQ_CLASS_LOWER, 
	r.ANALYTIC_METHOD_IDX, r.LAB_IDX, r.LAB_ANALYSIS_START_DT, 
	r.LAB_ANALYSIS_END_DT, r.LAB_ANALYSIS_TIMEZONE, r.RESULT_LAB_COMMENT_CODE, r.METHOD_DETECTION_LEVEL, r.LAB_REPORTING_LEVEL, r.PQL, r.LOWER_QUANT_LIMIT, r.UPPER_QUANT_LIMIT,
	r.DETECTION_LIMIT_UNIT, r.LAB_SAMP_PREP_IDX, r.LAB_SAMP_PREP_START_DT, r.LAB_SAMP_PREP_END_DT, r.DILUTION_FACTOR
	from T_WQX_IMPORT_TEMP_RESULT R, T_WQX_ACTIVITY A, T_WQX_IMPORT_TEMP_SAMPLE S
	where R.TEMP_SAMPLE_IDX = S.TEMP_SAMPLE_IDX 
	and S.TEMP_SAMPLE_IDX = A.TEMP_SAMPLE_IDX
	and R.IMPORT_STATUS_CD = 'P'
	and UPPER(S.USER_ID) = UPPER(@UserID);


	--insert characteristics into the org reference list if they do not yet exist
	DECLARE @OrgID varchar(30);
	select top 1 @OrgID = ORG_ID from T_WQX_IMPORT_TEMP_SAMPLE where UPPER(USER_ID) = UPPER(@UserID)

	insert into T_WQX_REF_CHAR_ORG (CHAR_NAME, ORG_ID, CREATE_USERID, CREATE_DT)
	select distinct R.char_name , @OrgID, 'SYSTEM', GetDate()
	from T_WQX_ACTIVITY A, T_WQX_RESULT R
	left join T_WQX_REF_CHAR_ORG O on R.CHAR_NAME = O.CHAR_NAME  and O.ORG_ID = @OrgID
	where R.ACTIVITY_IDX = A.ACTIVITY_IDX
	and A.ORG_ID = @OrgID
	and O.CHAR_NAME is null;


	--insert taxa into the org reference list if they do not yet exist
	insert into T_WQX_REF_TAXA_ORG ([BIO_SUBJECT_TAXONOMY], ORG_ID, CREATE_USERID, CREATE_DT)
	select distinct R.[BIO_SUBJECT_TAXONOMY] , @OrgID, 'SYSTEM', GetDate()
	from T_WQX_ACTIVITY A, T_WQX_RESULT R
	left join T_WQX_REF_TAXA_ORG O on R.[BIO_SUBJECT_TAXONOMY]= O.[BIO_SUBJECT_TAXONOMY]  and O.ORG_ID = @OrgID
	where R.ACTIVITY_IDX = A.ACTIVITY_IDX
	and A.ORG_ID = @OrgID
	and O.[BIO_SUBJECT_TAXONOMY] is null
	and NULLIF(R.BIO_SUBJECT_TAXONOMY,'') is not null;


	--update the entry type column for all imported sample
	UPDATE T_WQX_ACTIVITY set ENTRY_TYPE = 'C' where ENTRY_TYPE IS NULL;
	
	UPDATE T_WQX_ACTIVITY set ENTRY_TYPE = 'H' where ORG_ID = @OrgID and CREATE_DT > GetDate()-1 and
	(select count(*) from T_WQX_RESULT R where R.ACTIVITY_IDX = T_WQX_ACTIVITY.ACTIVITY_IDX and CHAR_NAME like '%RBP%') > 0;
	
	UPDATE T_WQX_ACTIVITY set ENTRY_TYPE = 'T' where ORG_ID = @OrgID and CREATE_DT > GetDate()-1 and
	(select count(*) from T_WQX_RESULT R where R.ACTIVITY_IDX = T_WQX_ACTIVITY.ACTIVITY_IDX and len(BIO_SUBJECT_TAXONOMY) > 0) > 0


	--DELETE TEMP DATA
	DELETE FROM T_WQX_IMPORT_TEMP_SAMPLE where UPPER(USER_ID) = UPPER(@UserID);



END


GO